﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using PenoyShop.Utilities;
using System.Data;

namespace PeonyShop.DAL
{
    public class DAImage :BaseDA
    {
        public DAImage()
        { 
        }

        public bool InsertImage(string sName, string sDesc, string sImgPath, int CateId, int SubCateId, int ProductId, int iNewsId, DateTime dtInsert, int iImgOrder, bool IsShowHome, bool IsShowOwn, bool IsActive, bool IsDelete)
        {
            bool bResult = false;
            DbCommand cmd;

                cmd = DB.GetStoredProcCommand("sp_InsertImage");
                DB.AddInParameter(cmd, "@Name", SqlDbType.NVarChar, sName);
                DB.AddInParameter(cmd, "@Desc", SqlDbType.NVarChar, sDesc);
                DB.AddInParameter(cmd, "@ImgPath", SqlDbType.NVarChar, sImgPath);
                DB.AddInParameter(cmd, "@ProductId", SqlDbType.Int, ProductId);
                DB.AddInParameter(cmd, "@NewsId", SqlDbType.Int, iNewsId);
                DB.AddInParameter(cmd, "@CateId", SqlDbType.Int, CateId);
                DB.AddInParameter(cmd, "@SubCateId", SqlDbType.Int, SubCateId);
                DB.AddInParameter(cmd, "@Date", SqlDbType.DateTime, dtInsert);
                DB.AddInParameter(cmd, "@ImgOrder", SqlDbType.Bit, iImgOrder);
                DB.AddInParameter(cmd, "@IsShowHome", SqlDbType.Bit, IsShowHome);
                DB.AddInParameter(cmd, "@IsShowOwn", SqlDbType.Bit, IsShowOwn);
                DB.AddInParameter(cmd, "@IsActive", SqlDbType.Bit, IsActive);
                DB.AddInParameter(cmd, "@IsDelete", SqlDbType.Bit, IsDelete);
            
            try
            {
                DB.ExecuteNonQuery(cmd);
                bResult = true;
            }
            catch (Exception ex)
            {
                Logger.FrameworkLogger.Error("System Error - InsertImage", ex);
                bResult = false;
            }
            return bResult;
        }


        public DataSet GetImage( string sName, int iMainCate, int iSubCate, int iProductId, int IsShowHome, int IsShowOwn, int IsActive, bool IsDelete)
        {
            DataSet ds = new DataSet();
            try {
                DbCommand cmd;
                cmd = DB.GetStoredProcCommand("sp_GetImages");
                

                if (!string.IsNullOrEmpty(sName))
                    DB.AddInParameter(cmd, "@Name", SqlDbType.NVarChar, sName);
                DB.AddInParameter(cmd, "@MainCate", SqlDbType.Int, iMainCate);
                if(iSubCate != -1)
                    DB.AddInParameter(cmd, "@SubCate", SqlDbType.Int, iSubCate);
                if(iProductId != -1)
                    DB.AddInParameter(cmd, "@ProductId", SqlDbType.Int, iProductId);
                if(IsShowHome != -1)
                    DB.AddInParameter(cmd, "@IsShowHome", SqlDbType.Bit, Convert.ToBoolean(IsShowHome));
                if(IsShowOwn != -1)
                    DB.AddInParameter(cmd, "@IsShowOwn", SqlDbType.Bit, Convert.ToBoolean(IsShowOwn));
                if(IsActive != -1)
                    DB.AddInParameter(cmd, "@IsActive", SqlDbType.Bit, Convert.ToBoolean(IsActive));
                DB.AddInParameter(cmd, "@IsDelete", SqlDbType.Bit, IsDelete);

                ds =  DB.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Logger.FrameworkLogger.Error("GetImage ",ex);
                return null;
            }
            return ds;
        }


        public DataSet GetImgByCate(int iMainCate, int iSubCate, int iProductId)
        {
            DataSet dsRes = new DataSet();
            try {
                string sql;
                
                if (iProductId == -1)
                {                    
                    sql = "select Id from Product where Product.MainCate=" + iMainCate;
                    if (iSubCate != -1)
                        sql += " and Product.SubCate=" + iSubCate;
                    DbCommand cmd = DB.GetSqlStringCommand(sql);
                    DataSet dsProid = DB.ExecuteDataSet(cmd);
                    if (dsProid != null)
                    {
                        DataTable dt = dsProid.Tables[0];
                        int len = dt.Rows.Count;
                        if (len > 0)
                        {

                            sql = "select * from Images where IsActive = 'True' and IsShowOwn = 'True' and  IsDelete = 'False' and ProductId IN (";
                            for (int i=0;i<len;i++)
                            {
                                DataRow dr = dt.Rows[i];
                                sql += dr["Id"].ToString();
                                if (i != len - 1)
                                {
                                    sql += ",";
                                }
                            }
                            sql += ")";
                        }
                        cmd = DB.GetSqlStringCommand(sql);
                        dsRes = DB.ExecuteDataSet(cmd);
                    }

                }
                else
                {
                    sql = "select * from Images where ProductId = " + iProductId + " and IsActive = true and IsShowOwn = true and  IsDelete = false";
                    DbCommand cmd = DB.GetSqlStringCommand(sql);
                    dsRes = DB.ExecuteDataSet(cmd);

                }
                

                
            }
            catch (Exception ex)
            {
                Logger.FrameworkLogger.Error("GetImgByCate ", ex);
                dsRes = null;
            }
            return dsRes; 
               
        }

        public DataTable GetImgHomePage(int CateHome, bool IsShowHome)
        {
            DataSet ds = new DataSet();

            DataTable dtResult = new DataTable();

            try
            {
                ds = GetImgByCate(CateHome, -1, -1);
                //Add more item in other page
                DbCommand cmd = DB.GetStoredProcCommand("sp_GetImgageHome");
                DB.AddInParameter(cmd, "@IsShowHome", SqlDbType.Bit, IsShowHome);
                DataSet dsOther = DB.ExecuteDataSet(cmd);
                if (dsOther != null)
                { 
                    DataTable dt = dsOther.Tables[0];
                    dtResult = dt.Clone();
                    int lenRow = dt.Rows.Count;
                    if (lenRow > 0)
                    {
                        for (int i = 0; i < lenRow; i++)
                        {
                            DataRow dr = dt.Rows[i];
                            dtResult.ImportRow(dr);
                        }
                    }
                }

                if (ds != null)
                {
                    DataTable dt = ds.Tables[0];
                    int lenRow = dt.Rows.Count;
                    if (lenRow > 0)
                    {
                        for (int i = 0; i < lenRow; i++)
                        {
                            DataRow dr = dt.Rows[i];
                            dtResult.ImportRow(dr);
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                Logger.FrameworkLogger.Error("GetImgHomePage ",ex);
                ds = null;
            }

            return dtResult;
        }

        public DataSet GetOtherImgShowHome(int IsActive, int IsShowHome)
        {
            DataSet ds = new DataSet();
            try
            {
                DbCommand cmd;
                cmd = DB.GetStoredProcCommand("sp_GetImgShowHome");
                if(IsActive != -1)
                DB.AddInParameter(cmd, "@IsActive", SqlDbType.Bit, Convert.ToBoolean(IsActive));
                if(IsShowHome != -1)
                DB.AddInParameter(cmd, "@IsShowHome", SqlDbType.Bit, Convert.ToBoolean(IsShowHome));
               
                ds = DB.ExecuteDataSet(cmd);
               

            }
            catch (Exception ex)
            {
                Logger.FrameworkLogger.Error("GetOtherImgShowHome ",ex);
                ds = null;
            }
            return ds;
        }

        public bool UpdateImage(int Id, string sName, string sDesc, string ImgPath, int iProductId, int Newsid, int iMainCateId, int iSubCateId, string sDate, int iImgOrder, int IsShowHome, int IsShowOwn, int IsActive, int IsDelete)
        {
            bool bResult = false;

            try {
                DbCommand cmd;
                cmd = DB.GetStoredProcCommand("sp_Updimage");

                DB.AddInParameter(cmd,"@Id", SqlDbType.Int, Id);

                if (!string.IsNullOrEmpty(sName))
                    DB.AddInParameter(cmd, "@Name", SqlDbType.NVarChar, sName);

                if (!string.IsNullOrEmpty(sDesc))
                    DB.AddInParameter(cmd, "@Desc", SqlDbType.NVarChar, sDesc);

                if (!string.IsNullOrEmpty(ImgPath))
                    DB.AddInParameter(cmd, "@ImgPath", SqlDbType.NVarChar, ImgPath);

                if (iProductId != -1)
                    DB.AddInParameter(cmd, "@ProductId", SqlDbType.Int, iProductId);

                if (Newsid != -1)
                    DB.AddInParameter(cmd, "@NewsId", SqlDbType.Int, Newsid);

                if (iMainCateId != -1)
                    DB.AddInParameter(cmd, "@CateId", SqlDbType.Int, iMainCateId);

                if (iSubCateId!= -1)
                    DB.AddInParameter(cmd, "@SubCateId", SqlDbType.Int, iSubCateId);

                if (!string.IsNullOrEmpty(sDate))
                    DB.AddInParameter(cmd, "@Date", SqlDbType.DateTime, DateTime.Parse(sDate));

                if (iImgOrder != -1)
                    DB.AddInParameter(cmd, "@ImgOrder", SqlDbType.Int, iImgOrder);

                if (IsShowHome != -1)
                    DB.AddInParameter(cmd, "@IsShowHome", SqlDbType.Bit, Convert.ToBoolean(IsShowHome));
                if (IsShowOwn != -1)
                    DB.AddInParameter(cmd, "@IsShowOwn", SqlDbType.Bit, Convert.ToBoolean(IsShowOwn));
                if (IsActive != -1)
                    DB.AddInParameter(cmd, "@IsActive", SqlDbType.Bit, Convert.ToBoolean(IsActive));
                if (IsDelete != -1)
                    DB.AddInParameter(cmd, "@IsDelete", SqlDbType.Bit, Convert.ToBoolean(IsDelete));

                DB.ExecuteNonQuery(cmd);
                bResult = true;
            }
            catch (Exception ex)
            {
                Logger.FrameworkLogger.Error("UpdateImage ",ex);
                bResult = false;
            }
            return bResult;
        }
    }
}
